In [ ]:
import pandas as pd
import plotly
plotly.offline.init_notebook_mode()
In [ ]:
t0 = pd.read_csv("negocios/todos.csv")

t1 = pd.read_csv("negocios/negocios_criados.csv")

t2 = pd.read_csv("negocios/agendamentos.csv")

t3 = pd.read_csv("negocios/agentamentos_aceitos.csv")

t4 = pd.read_csv("negocios/diagnostico.csv")

t5 = pd.read_csv("negocios/proposta.csv")

t6 = pd.read_csv("negocios/cliente.csv")

# t7 = pd.read_excel("data/06 cliente.xlsx", sheet_name='Contagem de Negócios   Cliente ')
In [ ]:
t0_t1 = t0.merge(t1, on='Record ID', how='left')


t0_t1 = t0_t1.rename(columns={
    'Nome do negócio_x': 'Nome do negócio',
    'Etapa do negócio_x': 'Etapa do negócio',
    'Valor na moeda da empresa_x': 'Valor na moeda da empresa',
    'Nome do negócio_y':'flag_corresponde_1',
    'Data de fechamento_x': 'Data de fechamento',
    'Pipeline_x': 'Pipeline',
}).assign(flag_corresponde_1 = lambda df_: df_['flag_corresponde_1'].notnull().map({True: "Sim", False: "Nao"}))\
    .drop(columns=['Valor na moeda da empresa_y', 'Etapa do negócio_y', 'Data de fechamento_y'])
In [ ]:
t0_t1_t2 = t0_t1.merge(t2, on='Record ID', how='left')

t0_t1_t2 = t0_t1_t2.rename(columns={
    'Nome do negócio_x': 'Nome do negócio',
    'Etapa do negócio_x': 'Etapa do negócio',
    'Valor na moeda da empresa_x': 'Valor na moeda da empresa',
    'Nome do negócio_y':'flag_corresponde_2',
    'Data de fechamento_x': 'Data de fechamento',
    'Pipeline_x': 'Pipeline',
}).assign(flag_corresponde_2 = lambda df_: df_['flag_corresponde_2'].notnull().map({True: "Sim", False: "Nao"}))\
    .drop(columns=['Valor na moeda da empresa_y', 'Etapa do negócio_y', 'Data de fechamento_y', 'Pipeline_y'])
In [ ]:
t0_t1_t2_t3 = t0_t1_t2.merge(t3, on='Record ID', how='left')\
    .rename(columns={
        'Nome do negócio_x': 'Nome do negócio',
        'Valor na moeda da empresa_x': 'Valor na moeda da empresa',
        'Etapa do negócio_x': 'Etapa do negócio',
        'Data de fechamento_x': 'Data de fechamento',
        'Nome do negócio_y': 'flag_corresponde_3',
        'Pipeline_x': 'Pipeline',
    })\
        .assign(flag_corresponde_3 = lambda df_: df_['flag_corresponde_3'].notnull().map({True: "Sim", False: "Nao"}))\
            .drop(columns=['Valor na moeda da empresa_y', 'Etapa do negócio_y', 'Data de fechamento_y', 'Pipeline_y'])
In [ ]:
t0_t1_t2_t3_t4 = t0_t1_t2_t3.merge(t4, on='Record ID', how='left')\
    .rename(columns={
        'Nome do negócio_x': 'Nome do negócio',
        'Valor na moeda da empresa_x': 'Valor na moeda da empresa',
        'Etapa do negócio_x': 'Etapa do negócio',
        'Data de fechamento_x': 'Data de fechamento',
        'Nome do negócio_y': 'flag_corresponde_4',
        'Pipeline_x': 'Pipeline',
    }).assign(flag_corresponde_4 = lambda df_: df_['flag_corresponde_4'].notnull().map({True: "Sim", False: "Nao"}))\
    .drop(columns=['Valor na moeda da empresa_y', 'Etapa do negócio_y', 'Data de fechamento_y', 'Pipeline_y'])
In [ ]:
t0_t1_t2_t3_t4_t5 = t0_t1_t2_t3_t4.merge(t5, on='Record ID', how='left')\
    .rename(columns={
        'Nome do negócio_x': 'Nome do negócio',
        'Valor na moeda da empresa_x': 'Valor na moeda da empresa',
        'Etapa do negócio_x': 'Etapa do negócio',
        'Data de fechamento_x': 'Data de fechamento',
        'Nome do negócio_y': 'flag_corresponde_5',
        'Pipeline_x': 'Pipeline',
    }).assign(flag_corresponde_5 = lambda df_: df_['flag_corresponde_5'].notnull().map({True: "Sim", False: "Nao"}))\
    .drop(columns=['Valor na moeda da empresa_y', 'Etapa do negócio_y', 'Data de fechamento_y', 'Pipeline_y'])
In [ ]:
t0_t1_t2_t3_t4_t5_t6 = t0_t1_t2_t3_t4_t5.merge(t6, on='Record ID', how='left')\
    .rename(columns={
        'Nome do negócio_x': 'Nome do negócio',
        'Valor na moeda da empresa_x': 'Valor na moeda da empresa',
        'Etapa do negócio_x': 'Etapa do negócio',
        'Data de fechamento_x': 'Data de fechamento',
        'Nome do negócio_y': 'flag_corresponde_6',
        'Pipeline_x': 'Pipeline',
    }).assign(flag_corresponde_6 = lambda df_: df_['flag_corresponde_6'].notnull().map({True: "Sim", False: "Nao"}))\
    .drop(columns=['Valor na moeda da empresa_y', 'Etapa do negócio_y', 'Data de fechamento_y', 'Pipeline_y'])
In [ ]:
t0_t1_t2_t3_t4_t5_t6 = t0_t1_t2_t3_t4_t5_t6\
    .assign(**{"Nome do negócio": lambda df_: df_['Nome do negócio'].str.strip()})\
    .rename(
    columns={
        'flag_corresponde_1': 'Negocios_Criados',
        'flag_corresponde_2': 'Agendamentos',
        'flag_corresponde_3': 'Agendamento_Aceito',
        'flag_corresponde_4': 'Diagnostico',
        'flag_corresponde_5': 'Proposta',
        'flag_corresponde_6': 'Cliente'
    }
)

#  'flag_corresponde_1': 'Agendamentos',
#         'flag_corresponde_2': 'Agendamento_Aceito',
#         'flag_corresponde_3': 'Diagnostico',
#         'flag_corresponde_4': 'Proposta',
#         'flag_corresponde_5': 'Cliente'
In [ ]:
empresas = pd.read_csv("negocios/empresas.csv")

negocios = pd.read_csv("negocios/todos.csv")

negocios = negocios.loc[:, ['Record ID', 'Nome do negócio', 'Valor', 'Valor do contrato anual', 'Motivo de fechamento perdido']]\
.assign(**{'Nome do negócio': lambda df: df['Nome do negócio'].str.upper()})

empresas = empresas.dropna(subset=['Nome da empresa'])
empresas = empresas.loc[:, ['Record ID', 'Nome da empresa', 'Setor']].drop("Record ID", axis=1)

empresas = empresas.assign(**{
    'Nome da empresa': lambda df_: df_['Nome da empresa'].str.strip()
})
empresas['Nome da empresa'] = empresas['Nome da empresa'].str.upper()
In [ ]:
negocios_record_nome = negocios.loc[:, ['Record ID', 'Nome do negócio']].assign(
    **{'Nome do negócio': lambda df_: df_['Nome do negócio'].str.strip().str.split(' - ').str[0].str.replace("NEGÓCIO",'')}
)
In [ ]:
import fuzzy_pandas as fpd
In [ ]:
# remove -
t0_t1_t2_t3_t4_t5_t6 = t0_t1_t2_t3_t4_t5_t6.assign(
    **{"Nome do negócio": lambda df: df['Nome do negócio'].str.replace('-', ' ')}
)


t0_t1_t2_t3_t4_t5_t6 = t0_t1_t2_t3_t4_t5_t6.assign(
    **{"Nome do negócio": lambda df: df['Nome do negócio'].apply(lambda s: ' '.join(s.split(' ')[:2]))}
)
empresas = empresas.assign(
    **{'Nome da empresa': lambda df: df['Nome da empresa'].apply(lambda s: ' '.join(s.split(' ')[:2]))}
)
In [ ]:
funil_negocios = t0_t1_t2_t3_t4_t5_t6.merge(negocios, on='Record ID', how='left')\
    .rename(columns={
        'Nome do negócio_x': 'Nome do negócio',
        })\
    .drop(columns=['Nome do negócio_y'])\
    .assign(**{'Nome do negócio': lambda df: df['Nome do negócio'].str.upper()})
In [ ]:
 
In [ ]:
# fuzzy matching

results = fpd.fuzzy_merge(funil_negocios, empresas, left_on='Nome do negócio', right_on='Nome da empresa',
                          join='left-outer',
                          method = 'levenshtein', threshold = 0.8)
    # .merge(empresas.assign(**{'Nome da empresa': lambda df: df['Nome da empresa'].str.upper()}), left_on='Nome do negócio', right_on='Nome da empresa', how='left')\
    # .rename(columns={
    #     'Record ID_x': "Record ID"
    # })\
    # .loc[:, ['Record ID', 'Nome do negócio', 'Data de criação', 'Data de fechamento', 'ultima_etapa', 'Valor', 'Valor do contrato anual', 'Motivo de fechamento perdido', 'Setor']]


print("Found", results.shape)
results.sample(15)
Found (492, 26)
Out[ ]:
Record ID Nome do negócio Data de criação Data de fechamento Etapa do negócio Valor_x Valor do contrato anual_x Receita anual recorrente Valor total do contrato Valor previsto ... Agendamentos Agendamento_Aceito Diagnostico Proposta Cliente Valor_y Valor do contrato anual_y Motivo de fechamento perdido_y Nome da empresa Setor
255 7059246430 BANCO NEXT 2021-12-01 17:43 2021-12-31 17:43 Negócio perdido 808140.00 NaN NaN NaN 808140.00 ... Nao Nao Nao Nao Nao 808140.00 NaN Timing; Desinteresse/Declinou BANCO NEXT NaN
376 4668934210 EUROFARMA 2021-03-15 17:45 2021-07-15 12:10 Negócio perdido 276000.00 NaN NaN NaN 276000.00 ... Sim Sim Sim Sim Nao 276000.00 NaN Nós Declinamos EUROFARMA Farmacêutica
25 13912182170 DENTSPLY SIRONA 2023-06-29 14:54 NaN Negócio perdido NaN NaN NaN NaN NaN ... Sim Sim Nao Nao Nao NaN NaN Financeiro; Timing; Não é ICP DENTSPLY SIRONA NaN
353 5239041433 SYNGENTA 2021-05-12 11:02 2021-06-21 14:25 Cliente 187500.00 NaN NaN NaN 187500.00 ... Sim Sim Sim Sim Sim 187500.00 NaN NaN SYNGENTA NaN
28 13898389069 VITTIA 2023-06-28 09:40 NaN Proposta Enviada (SQL) 186000.00 NaN NaN NaN 186000.00 ... Sim Sim Sim Sim Nao 186000.00 NaN NaN VITTA NaN
472 5691587426 NIDERA 2021-07-15 10:49 2022-02-15 15:10 Negócio perdido NaN NaN NaN NaN NaN ... Sim Sim Sim Nao Nao NaN NaN Sumiu
66 12688511828 VERDE AGRITECH 2023-03-23 12:58 NaN Negócio perdido NaN NaN NaN NaN NaN ... Sim Sim Nao Nao Nao NaN NaN Maturação; Timing VERDE AGRITECH NaN
338 5381991819 OTTOBOCK 2021-06-01 10:43 2021-09-22 14:02 Negócio perdido 251055.60 NaN NaN NaN 251055.60 ... Sim Sim Sim Sim Sim 251055.60 NaN Financeiro; Desinteresse/Declinou OTTOBOCK Farmacêutica
260 6939016495 ORAL SIN 2021-11-16 17:20 2022-05-17 16:25 Negócio perdido 458150.16 NaN NaN NaN 458150.16 ... Sim Sim Sim Sim Nao 458150.16 NaN Fechou com Concorrente; Financeiro ORAL SIN NaN
367 4985313037 SANTA HELENA 2021-04-12 14:41 2021-05-31 12:47 Negócio perdido 227936.16 NaN NaN NaN 227936.16 ... Sim Sim Sim Sim Nao 227936.16 NaN Fechou com Concorrente; Financeiro SANTA HELENA Produção de alimentos
116 10358562966 NEW HOLLAND 2022-09-29 15:53 NaN Negócio perdido NaN NaN NaN NaN NaN ... Sim Sim Sim Nao Nao NaN NaN Timing; Maturação NEW HOLLAND Maquinário
7 14109118720 HUDL 2023-07-13 11:46 NaN Agendamentos (MQL) NaN NaN NaN NaN NaN ... Sim Nao Nao Nao Nao NaN NaN NaN HUDL Software de computador
411 3086522280 EUROFARMA 2020-10-08 14:56 2021-01-12 14:34 Negócio perdido 20964.00 NaN NaN NaN 20964.00 ... Sim Sim Sim Sim Sim 20964.00 NaN Não era decisor EUROFARMA Farmacêutica
185 8437713017 EUCATUR 2022-04-01 15:20 2022-04-06 11:49 Negócio perdido NaN NaN NaN NaN NaN ... Sim Sim Nao Nao Nao NaN NaN Financeiro; Não é ICP EUCATUR Transporte/caminhões/ferrovias
223 7992382761 DONIZETE DISTRIBUIDORA 2022-02-22 11:31 2022-04-28 14:26 Negócio perdido NaN NaN NaN NaN NaN ... Sim Sim Nao Nao Nao NaN NaN Não é ICP; Sumiu; Nós Declinamos ONIZ DISTRIBUIDORA Atacado

15 rows × 26 columns

In [ ]:
merged = results.merge(negocios_record_nome, on='Record ID', how='left')


merged.loc[merged['Setor'].isnull()].loc[:, ['Record ID', 'Nome do negócio_y']].to_csv("negocios_sem_setor_completo.csv", index=False)
In [ ]:
negocios_setor = pd.read_csv("negocios_sem_setor_completo.csv")
In [ ]:
results = results.merge(negocios_setor, on='Record ID', how='left').assign(
    Setor = lambda df: df['Setor_x'].combine_first(df['Setor_y'])
).drop(columns=['Setor_x', 'Setor_y'])
In [ ]:
results.to_csv("negocios_completo.csv", index=False)
In [ ]:
results.columns
Out[ ]:
Index(['Record ID', 'Nome do negócio', 'Data de criação', 'Data de fechamento',
       'Etapa do negócio', 'Valor', 'Valor do contrato anual',
       'Receita anual recorrente', 'Valor total do contrato', 'Valor previsto',
       'Valor na moeda da empresa',
       'Valor do negócio projetado na moeda local',
       'Valor do negócio projetado', 'Motivo de fechamento perdido',
       'Nome da empresa', 'Setor', 'ultima_etapa', 'Cliente',
       'Nome do negócio completo'],
      dtype='object')
In [ ]:
results['ultima_etapa'] = results[['Negocios_Criados','Agendamentos', 'Agendamento_Aceito', 'Diagnostico', 'Proposta', 'Cliente']].apply(
    lambda row: next((coluna for coluna in ['Cliente', 'Proposta', 'Diagnostico', 'Agendamento_Aceito', 'Agendamentos', 'Negocios_Criados'] if row[coluna] == 'Sim'), 'Contato'), axis=1)
In [ ]:
results = results.drop(columns=['Valor_y', 'Valor do contrato anual_y','Pipeline', 'Motivo de fechamento perdido_y',
                      'Negocios_Criados','Agendamentos', 'Agendamento_Aceito', 'Diagnostico', 'Proposta', 'Cliente']).rename(columns={
    'Valor_x': 'Valor',
    'Valor do contrato anual_x': 'Valor do contrato anual',
    'Nome do negócio_y': 'Nome do negócio completo',
    'Motivo de fechamento perdido_x': 'Motivo de fechamento perdido',
    })#.to_csv("negocios/silver/negocios_ultima_etapa.csv", index=False)

Graficos¶

In [ ]:
import pandas as pd
In [ ]:
results = pd.read_csv("negocios/silver/negocios_ultima_etapa.csv")
In [ ]:
results = results.drop(columns='Nome do negócio completo').merge(t0.loc[:, ['Record ID', 'Nome do negócio']], on='Record ID', how='left').rename(columns={
    'Nome do negócio_x': 'Nome do negócio',
    'Nome do negócio_y': 'Nome do negócio completo'
})

Contagem de Motivos

In [ ]:
serie_dividida = results['Motivo de fechamento perdido'].fillna('Desconhecido').str.split(';')

# Crie uma nova série com cada motivo em uma linha separada
nova_serie = pd.Series([motivo.strip() for motivos in serie_dividida for motivo in motivos])

nova_serie.value_counts().sort_values().plot(kind='barh', figsize=(10, 5))
Out[ ]:
<Axes: >

Data de criacao vs segmento

In [ ]:
# results['Nome do negócio completo'] = results['Nome do negócio completo'].combine_first(results['Nome do negócio'])
In [ ]:
import pandas as pd
import plotly.express as px


# Converter a coluna 'Data' para o tipo datetime
results['Data de criação'] = pd.to_datetime(results['Data de criação'])
results['Setor'] = results['Setor'].str.strip()
results['Cliente'] = (results['ultima_etapa'] == 'Cliente').map({True: 'Sim', False: 'Nao'})


fig = px.scatter(results, x='Data de criação', y='Setor', color='Cliente', symbol='Cliente',
                 color_discrete_map={'Sim': 'green', 'Nao': 'red'},
                 title='Gráfico de Setores por Data',
                 labels={'Data': 'Data', 'Setor': 'Setor', 'Cliente': 'Cliente'},
                 category_orders={'Setor': ['A', 'B', 'C'], 'Cliente': ['Sim', 'Não']},
                 hover_data=['Nome do negócio completo'],
                 width=1000, height=1100)

# Configuração de formatação do eixo X
fig.update_xaxes(showgrid=True, tickangle=45)

# Exibir o gráfico interativo
fig.show()
In [ ]:
import chart_studio.plotly as py
import chart_studio

chart_studio.tools.set_credentials_file(username = 'levyvix',   api_key = 'UqhjaEHoOL14JH5joQAT')
In [ ]:
# py.plot(fig, filename = 'grafico_setores_por_data', auto_open=True)
Out[ ]:
'https://plotly.com/~levyvix/1/'

taxa de conversao por segmento

In [ ]:
# taxa de conversao por segmento (setor)
# quantos entraram e quantos virarm clientes
import plotly.graph_objects as go

taxa_conversao_setor = (results.groupby("Setor")['Cliente'].value_counts().unstack().fillna(0).assign(
    **{'Taxa de conversao': lambda df: df['Sim'] / (df['Sim'] + df['Nao'])}
)
.sort_values(by='Taxa de conversao', ascending=False)
)

fig = go.Figure()

# Adicionar as barras para cada setor
fig.add_trace(go.Bar(x=taxa_conversao_setor.index, y=taxa_conversao_setor['Taxa de conversao'],
                     marker_color='blue', text=taxa_conversao_setor['Taxa de conversao'].round(2), textposition='auto'))

# Configurações do layout do gráfico
fig.update_layout(title='Taxa de Conversão por Setor',
                  xaxis_title='Setor',
                  yaxis_title='Taxa de Conversão',
                  xaxis=dict(tickangle=45))

# Exibir o gráfico interativo
fig.show()
In [ ]:
# py.plot(fig, filename = 'taxa_de_conversao_por_segmento', auto_open=True)
Out[ ]:
'https://plotly.com/~levyvix/3/'
In [ ]:
import pandas as pd
import plotly.express as px

for segment, group in results.groupby('Setor'):

  fig = px.scatter(group, x='Data de criação', y='Nome do negócio completo',
                   color='Cliente', symbol='Cliente', hover_data=['Data de criação'])

  fig.add_trace(px.scatter(group, x='Data de fechamento', y='Nome do negócio completo',
                hover_data=['Data de fechamento']).data[0])
               
  for name in group['Nome do negócio completo'].unique():
    df_name = group[group['Nome do negócio completo'] == name]
    
    creation_date = df_name['Data de criação'].iat[0]
    closing_date = df_name['Data de fechamento'].iat[0]

    fig.add_trace(px.line(x=[creation_date, closing_date], y=[name, name]).data[0])
    
    fig.update_traces(line=dict(color='rgb(100,100,100)', width=1), selector=dict(type='line'))

  fig.update_layout(title=f'Start and End Dates for {segment}')

  fig.show()
In [ ]:
# py.plot(fig, filename = 'criacao_fechamento_segmento', auto_open=True)
Out[ ]:
'https://plotly.com/~levyvix/6/'